package cn.edu.seu.ssny.dao;

import cn.edu.seu.ssny.dao.support.IBaseDao;
import cn.edu.seu.ssny.entity.Device;
import cn.edu.seu.ssny.entity.DeviceDataStd;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;
import java.util.Optional;

@Repository
public interface IDeviceDataStdDao extends IBaseDao<DeviceDataStd, Integer> {
    Optional<DeviceDataStd> findFirstByDeviceOrderByCreateTimeDesc(Device device);

    Optional<DeviceDataStd> findByDeviceAndCreateTime(Device device, Date createTime);

    List<DeviceDataStd> findAllByDeviceAndCreateTimeBetween(Device device, Date start, Date end);

    List<DeviceDataStd> findAllByDeviceAndCreateTimeBetweenOrderByCreateTimeAsc(Device device, Date start, Date end);

    List<DeviceDataStd> findAllByDeviceAndCreateTimeBetweenOrderByCreateTimeDesc(Device device, Date day0, Date date);

    @Query(value = "SELECT * FROM tb_device_data_std_day WHERE device_id in ?1 ORDER BY create_time DESC ", nativeQuery = true)
    List<DeviceDataStd> findAllByDeviceInAndCreateTimeDesc(List<Integer> deviceIds);

    /**
     * 功能描述:
     * <根据采集点的ID查找出所有采集点的数据
     * <并且对于每个采集点按照日期倒排的最新的一条数据
     * <MySQL的GROUP BY不加聚合函数时，默认会处理成LIMIT 1
     *
     * @param deviceIds
     * @return
     */
    @Query(value = "SELECT * FROM (SELECT * FROM tb_device_data_std_day WHERE device_id in ?1 ORDER BY create_time DESC) as temp GROUP BY temp.device_id; ", nativeQuery = true)
    List<DeviceDataStd> findAllByDeviceInAndCreateTimeDescAndLimitOnePerDevice(List<Integer> deviceIds);

    /*向主表中存记录*/
    @Modifying
    @Query(value = "INSERT INTO tb_device_data_std VALUES (null,?1,?2,?3,?4)", nativeQuery = true)
    int insert(Date createTime, Double data, int deviceId, int deviceDataId);

    /*向年记录表中存储数据  只存储每一年最开始的数据*/
    @Modifying
    @Query(value = "INSERT INTO tb_device_data_std_year VALUES (null,?1,?2,?3,?4)", nativeQuery = true)
    int insertYear(Date createTime, Double data, int deviceId, int deviceDataId);

    /*向月记录表中存储数据  只存储每一月最开始的数据*/
    @Modifying
    @Query(value = "INSERT INTO tb_device_data_std_month VALUES (null,?1,?2,?3,?4)", nativeQuery = true)
    int insertMonth(Date createTime, Double data, int deviceId, int deviceDataId);

    /*向天记录表中添加数据 只存储每一天最开始的数据*/
    @Modifying
    @Query(value = "INSERT INTO tb_device_data_std_day VALUES (null,?1,?2,?3,?4)", nativeQuery = true)
    int insertDay(Date createTime, Double data, int deviceId, int deviceDataId);

    /*向时记录表中添加数据 只存储每一时最开始的数据*/
    @Modifying
    @Query(value = "INSERT INTO tb_device_data_std_hour VALUES (null,?1,?2,?3,?4)", nativeQuery = true)
    int insertHour(Date createTime, Double data, int deviceId, int deviceDataId);


    /*更新主表*/
    @Modifying
    @Query(value = "UPDATE tb_device_data_std SET data=?1,device_data_id=?2,device_id=?3 where id=?4", nativeQuery = true)
    int update(Double data, int deviceDataId, int deviceId, int id);

    /*更新年记录表*/
    @Modifying
    @Query(value = "UPDATE tb_device_data_std_year SET data=?1,device_data_id=?2,device_id=?3 where id=?4", nativeQuery = true)
    int updateYear(Double data, int deviceDataId, int deviceId, int id);

    /*更新月记录表*/
    @Modifying
    @Query(value = "UPDATE tb_device_data_std_month SET data=?1,device_data_id=?2,device_id=?3 where id=?4", nativeQuery = true)
    int updateMonth(Double data, int deviceDataId, int deviceId, int id);

    /*更新天记录表*/
    @Modifying
    @Query(value = "UPDATE tb_device_data_std_day SET data=?1,device_data_id=?2,device_id=?3 where id=?4", nativeQuery = true)
    int updateDay(Double data, int deviceDataId, int deviceId, int id);

    /*更新时记录表*/
    @Modifying
    @Query(value = "UPDATE tb_device_data_std_hour SET data=?1,device_data_id=?2,device_id=?3 where id=?4", nativeQuery = true)
    int updateHour(Double data, int deviceDataId, int deviceId, int id);

    /*删除年表中不属于每年开始的数据  主要用于不断的存储当年的数据 删除旧的 添加最新的  但是为了系统的可用性，不能删除所有的，需要留下最近的，所以最好删除两天以前的*/
    @Modifying
    @Query(value = "DELETE FROM tb_device_data_std_year where create_time NOT LIKE '%-01-01 00:00:00'", nativeQuery = true)
    int deleteFromYear();

    /*删除月表中不属于每月开始的数据  主要用于不断的存储当年的数据 删除旧的 添加最新的*/
    @Modifying
    @Query(value = "DELETE FROM tb_device_data_std_month where create_time NOT LIKE '%-01 00:00:00'", nativeQuery = true)
    int deleteFromMonth();

    /*删除天表中不属于每天开始的数据  主要用于不断的存储当年的数据 删除旧的 添加最新的*/
    @Modifying
    @Query(value = "DELETE FROM tb_device_data_std_day where create_time NOT LIKE '% 00:00:00' OR create_time<(SELECT DATE_SUB(CURDATE(),INTERVAL ?1 DAY))", nativeQuery = true)
    int deleteFromDay(int interval);

    /*删除时表中不属于每时开始的数据  主要用于不断的存储当年的数据 删除旧的 添加最新的*/
    @Modifying
    @Query(value = "DELETE FROM tb_device_data_std_hour where create_time NOT LIKE '%:00:00' OR create_time<(SELECT DATE_SUB(CURDATE(),INTERVAL ?1 DAY))", nativeQuery = true)
    int deleteFromHour(int interval);

    /*删除天记录表中 interval天之前的数据*/
    @Transactional
    @Modifying
    @Query(value = "DELETE FROM tb_device_data_std_day WHERE create_time < (SELECT DATE_SUB(CURDATE(),INTERVAL ?1 DAY))", nativeQuery = true)
    void deleteAgoDataFromTbDeviceDataStd(int interval);

    /**
     * 功能描述:
     * <返回hour表中从startDate到endDate之间的所有满足整点的数据，以及最新的一条数据
     * <要求endDate和最新一条数据之间不要相差一小时
     * <一个项目的采集点数目不超过10000条才能正常运行
     *
     * @param deviceIds
     * @param startDate
     * @param endDate
     * @return
     */
    @Query(value = "SELECT * FROM ((SELECT * FROM (SELECT * FROM tb_device_data_std_hour WHERE device_id in ?1 ORDER BY create_time DESC LIMIT 10000) AS temp GROUP BY temp.device_id) UNION (SELECT * FROM tb_device_data_std_hour WHERE device_id in ?1 AND create_time BETWEEN ?2 AND ?3 AND create_time like '%:00:00' ORDER BY create_time DESC)) as temp_a ORDER BY create_time ASC", nativeQuery = true)
    List<DeviceDataStd> findAllByDeviceInAndHourOrderByCreateTimeASC(List<Integer> deviceIds, Date startDate, Date endDate);

    /**
     * 功能描述:
     * <返回hour表中从startDate到endDate之间的所有满足整天的数据，以及最新的一条数据
     * <要求endDate和最新一条数据之间不要相差一天
     * <一个项目的采集点数目不超过10000条才能正常运行
     *
     * @param deviceIds
     * @param startDate
     * @param endDate
     * @return
     */
    @Query(value = "SELECT * FROM ((SELECT * FROM (SELECT * FROM tb_device_data_std_day WHERE device_id in ?1 ORDER BY create_time DESC LIMIT 10000) AS temp GROUP BY temp.device_id) UNION (SELECT * FROM tb_device_data_std_day WHERE device_id in ?1 AND create_time BETWEEN ?2 AND ?3 AND create_time like '% 00:00:00' ORDER BY create_time DESC)) as temp_a ORDER BY create_time ASC", nativeQuery = true)
    List<DeviceDataStd> findAllByDeviceInAndDayOrderByCreateTimeASC(List<Integer> deviceIds, Date startDate, Date endDate);

    /**
     * 功能描述:
     * <返回hour表中从startDate到endDate之间的所有满足整月的数据，以及最新的一条数据
     * <要求endDate和最新一条数据之间不要相差一月
     * <一个项目的采集点数目不超过10000条才能正常运行
     *
     * @param deviceIds
     * @param startDate
     * @param endDate
     * @return
     */
    @Query(value = "SELECT * FROM ((SELECT * FROM (SELECT * FROM tb_device_data_std_month WHERE device_id in ?1 ORDER BY create_time DESC LIMIT 10000) AS temp GROUP BY temp.device_id) UNION (SELECT * FROM tb_device_data_std_month WHERE device_id in ?1 AND create_time BETWEEN ?2 AND ?3 AND create_time like '%-01 00:00:00' ORDER BY create_time DESC)) as temp_a ORDER BY create_time ASC", nativeQuery = true)
    List<DeviceDataStd> findAllByDeviceInAndMonthOrderByCreateTimeASC(List<Integer> deviceIds, Date startDate, Date endDate);

    /**
     * 功能描述:
     * <返回hour表中从startDate到endDate之间的所有满足整年的数据，以及最新的一条数据
     * <要求endDate和最新一条数据之间不要相差一年
     * <一个项目的采集点数目不超过10000条才能正常运行
     *
     * @param deviceIds
     * @param startDate
     * @param endDate
     * @return
     */
    @Query(value = "SELECT * FROM ((SELECT * FROM (SELECT * FROM tb_device_data_std_year WHERE device_id in ?1 ORDER BY create_time DESC LIMIT 10000) AS temp GROUP BY temp.device_id) UNION (SELECT * FROM tb_device_data_std_month WHERE device_id in ?1 AND create_time BETWEEN ?2 AND ?3 AND create_time like '%-01-01 00:00:00' ORDER BY create_time DESC)) as temp_a ORDER BY create_time ASC", nativeQuery = true)
    List<DeviceDataStd> findAllByDeviceInAndYearOrderByCreateTimeASC(List<Integer> deviceIds, Date startDate, Date endDate);

    /**
     * <功能描述:
     * 在hour表中，查询出每一个deviceIds最新的一条数据，并且对这些数据进行求和
     *
     * @param deviceIds
     * @return
     */
    @Query(value = "SELECT SUM(data) FROM (SELECT device_id,MAX(create_time) as create_time  FROM tb_device_data_std_hour WHERE device_id IN ?1 GROUP BY device_id) a LEFT JOIN (SELECT * FROM tb_device_data_std_hour WHERE device_id in ?1) b on a.device_id=b.device_id and a.create_time=b.create_time;", nativeQuery = true)
    Double getSumOfRecentOneDataOfDevicesByDeviceInInHourTable(List<Integer> deviceIds);
}


